# 第二题与第三题的代码, byTLHX, 2022-12-1
import pandas as pd
import os

# 第二题
pd.set_option("display.unicode.east_asian_width", True)
df_mashup = pd.read_csv("Correted-ProgrammableWeb-dataset-main/data/mashup_nodes_estimator.csv", on_bad_lines='skip',
                        sep='\t', engine='python')
df_mashup_invoking_APIs = pd.read_csv("Correted-ProgrammableWeb-dataset-main/data/m-a_edges.csv", on_bad_lines='skip',
                                      sep='\t', engine='python')
df_API = pd.read_csv("Correted-ProgrammableWeb-dataset-main/data/api_nodes_estimator.csv", on_bad_lines='skip',
                     sep='\t', engine='python')

# 通过 mashup_name 找到对应的 Category 的字典
dt_mashup_category = {}
for i in range(len(df_mashup.index)):
    series = df_mashup.iloc[i]
    mashup_name, category = series['name'], series['c']
    dt_mashup_category[mashup_name] = category

api_category_set = set()  # 存在的的 apiCategory 的集合

# 通过 api_url 找到对应的 Category 的字典
dt_api_category = {}
for i in range(len(df_API.index)):
    series = df_API.iloc[i]
    api_url, category = series['url'], series['c']
    dt_api_category[api_url] = category
    api_category_set.add(category)

# 根据 mashupCategory 查找其可使用的全部 apiCategory (可以有多组，结果在集合中) 的字典
dt_mashupCategory_allApiCategory = {}

# 根据 mashupCategory 查找其对应的全部 mashup, 并且可进一步根据该 mashup 查找对应的 apiCategory 的字典
dt_mashupCategory_allMashup_apiCategory = {}  # 字典嵌套字典再嵌套集合

for i in range(len(df_mashup_invoking_APIs.index)):
    series = df_mashup_invoking_APIs.iloc[i]
    mashup_name, api_url = series['source'], series['target']
    mashup_category = dt_mashup_category.get(mashup_name, None)
    api_category = dt_api_category.get(api_url, None)
    if mashup_category == None or api_category == None:  # 异常数据丢弃
        continue
    # 写入 dt_mashupCategory_allApiCategory 字典
    if mashup_category not in dt_mashupCategory_allApiCategory.keys():
        dt_mashupCategory_allApiCategory[mashup_category] = {api_category}
    else:
        dt_mashupCategory_allApiCategory[mashup_category].add(api_category)

    # 写入 dt_mashupCategory_allMashup_apiCategory 字典
    if mashup_category not in dt_mashupCategory_allMashup_apiCategory.keys():
        dt_mashupCategory_allMashup_apiCategory[mashup_category] = {mashup_name: {api_category}}
    else:
        if mashup_name not in dt_mashupCategory_allMashup_apiCategory[mashup_category].keys():
            dt_mashupCategory_allMashup_apiCategory[mashup_category][mashup_name] = {api_category}
        else:
            dt_mashupCategory_allMashup_apiCategory[mashup_category][mashup_name].add(api_category)

# 根据 mashupCategory 查找其一定使用的 apiCategory 的字典
dt_mashupCategory_mustUsedApiCategory = {}
for mashupCategory in dt_mashupCategory_allMashup_apiCategory.keys():
    # 这里很重要! 必须使用 copy, 否则会影响到原始集合
    mustUseApiCategory = dt_mashupCategory_allApiCategory[mashupCategory].copy()
    for apiCategorySet in dt_mashupCategory_allMashup_apiCategory[mashupCategory].values():
        mustUseApiCategory &= apiCategorySet
    dt_mashupCategory_mustUsedApiCategory[mashupCategory] = mustUseApiCategory

# 根据 mashupCategory 查找其使用数量降序排列的 apiCategory 的字典, 其元素为列表(从左到右为 apiCategory 使用次数降序排列)
# 该列表的元素为元组, 从左到右为 apiCategory 使用次数降序排列, 以及它们对应的使用次数, 如 [('Mapping', 897), ('Social', 93), ('Photos', 70), ...]
dt_mashupCategory_mostUsedApiCategory = {}
for mashupCategory in dt_mashupCategory_allApiCategory.keys():
    if mashupCategory not in dt_mashupCategory_mostUsedApiCategory.keys():
        dt_mashupCategory_mostUsedApiCategory[mashupCategory] = {}
    for apiCategorySet in dt_mashupCategory_allMashup_apiCategory[mashupCategory].values():
        for apiCategory in apiCategorySet:
            dt_mashupCategory_mostUsedApiCategory[mashupCategory][apiCategory] = dt_mashupCategory_mostUsedApiCategory[
                                                                                     mashupCategory].get(apiCategory,
                                                                                                         0) + 1
    dt_mashupCategory_mostUsedApiCategory[mashupCategory] = sorted(
        list(dt_mashupCategory_mostUsedApiCategory[mashupCategory].items()), key=lambda x: -x[1])

# mashupCategory 与 apiCategory 的使用数量表, 即通过行列标签确定一组 mashup 类别和 api 类别对应的元素, 该元素即为它们共同使用的次数
mashupCategory_apiCategory_countTable = {}
for mashupCategory in dt_mashupCategory_allApiCategory.keys():
    mashupCategory_apiCategory_countTable[mashupCategory] = {}
    for apiCategory in api_category_set:
        mashupCategory_apiCategory_countTable[mashupCategory][apiCategory] = 0
    for apiCategoryAndCount in dt_mashupCategory_mostUsedApiCategory[mashupCategory]:
        mashupCategory_apiCategory_countTable[mashupCategory][apiCategoryAndCount[0]] = apiCategoryAndCount[1]

# 制作并写入 mashup 类别与 api 类别的使用数量对照表
indexTitle = list(dt_mashupCategory_allApiCategory.keys())  # 行为 mashup 类别
columnsTitle = list(api_category_set)  # 列为 api 类别
countTable_data = [[mashupCategory_apiCategory_countTable[iT][cT] for cT in columnsTitle] for iT in indexTitle]
df_mashupCategory_apiCategory_countTable = pd.DataFrame(data=countTable_data, index=indexTitle, columns=columnsTitle)
filename = "mashup类别与api类别使用数量对照表.xlsx"
if not os.path.exists("secondQuestion_answer_data/" + filename):
    df_mashupCategory_apiCategory_countTable.to_excel("secondQuestion_answer_data/" + filename, sheet_name=filename)
else:
    print(filename + " 已存在!")

# mashupCategory(mashup 类别) 与 apiCategory(api 类别) 的总关系表(即 relationTable), 每行元素含义如下:
# mashup类别, 对应mashup数, 有关的全部api类别数, 对应的必用到的 api 类别数, 对应的必用到的 api 类别(无则为'None'),
# api 类别使用次数前三名降序排列以及它们的使用次数
mashupCategory_apiCategory_relationTable = []

# mashup 类别与有关的全部 api 类别数对照表
mashupCategory_possibleApiCategoryCount_Table = []

# mashup 类别与其对应的必用到的 api 类别、类别数对照表
mashupCategory_mustUseApiCategory_Table = []

# mashup 类别与其使用的 api 类别次数降序排列以及它们的使用次数对照表
mashupCategory_mostUsedApiCategory_Table = []

for mashupCategory in dt_mashupCategory_allApiCategory.keys():
    possibleApiCategoryCount = len(dt_mashupCategory_allApiCategory[mashupCategory])  # 有关的全部api类别数
    mashupCount = len(dt_mashupCategory_allMashup_apiCategory[mashupCategory])  # 对应mashup数
    mustUseApiCategory = ",".join(dt_mashupCategory_mustUsedApiCategory[mashupCategory])  # 对应的必用到的 api 类别
    mustUseApiCategory = "None" if mustUseApiCategory == "" else mustUseApiCategory
    mustUseApiCategoryCount = len(dt_mashupCategory_mustUsedApiCategory[mashupCategory])  # 对应的必用到的 api 类别数
    mostUsedApiCategory = []  # api 类别使用次数前三名降序排列, 以及它们的使用次数
    for apiCategoryAndCount in dt_mashupCategory_mostUsedApiCategory[mashupCategory][:3]:  # 限制为前 3 名
        mostUsedApiCategory.append(apiCategoryAndCount[0] + ':' + str(apiCategoryAndCount[1]))
    mostUsedApiCategory = ", ".join(mostUsedApiCategory)
    # 写入 mashupCategory 与 apiCategory 的总关系表(即 relationTable)
    mashupCategory_apiCategory_relationTable.append([mashupCategory, mashupCount, possibleApiCategoryCount,
                                                     mustUseApiCategoryCount, mustUseApiCategory, mostUsedApiCategory])

    # 写入 mashup 类别与有关的全部 api 类别数对照表
    mashupCategory_possibleApiCategoryCount_Table.append([mashupCategory, possibleApiCategoryCount])

    # 写入 mashup 类别与其对应的必用到的 api 类别、类别数对照表
    if mustUseApiCategory != 'None':
        mashupCategory_mustUseApiCategory_Table.append([mashupCategory, mustUseApiCategory, mustUseApiCategoryCount])

    # 写入 mashup 类别与其使用的 api 类别次数降序排列以及它们的使用次数对照表
    mostUsedApiCategory = []  # api 类别使用次数降序排列, 以及它们的使用次数
    for apiCategoryAndCount in dt_mashupCategory_mostUsedApiCategory[mashupCategory]:
        mostUsedApiCategory.append(apiCategoryAndCount[0] + ':' + str(apiCategoryAndCount[1]))
    mostUsedApiCategory = ", ".join(mostUsedApiCategory)
    mashupCategory_mostUsedApiCategory_Table.append([mashupCategory, mostUsedApiCategory])

# 制作并写入 mashupCategory(mashup 类别) 与 apiCategory(api 类别) 的总关系表(即 relationTable)
df_mashupCategory_apiCategory_relationTable = pd.DataFrame(data=mashupCategory_apiCategory_relationTable,
                                                           columns=['mashup类别', '对应mashup数', '有关的全部api类别数',
                                                                    '必用到的api类别数', '必用到的api类别',
                                                                    'api类别使用次数前三名,冒号后为使用次数'])
filename = "mashup类别与api类别总关系表.xlsx"
if not os.path.exists("secondQuestion_answer_data/" + filename):
    df_mashupCategory_apiCategory_relationTable.to_excel("secondQuestion_answer_data/" + filename, sheet_name=filename,
                                                         index=False)
else:
    print(filename + " 已存在!")

# 制作并写入 mashup 类别与有关的全部 api 类别数对照表
mashupCategory_possibleApiCategoryCount_Table.sort(key=lambda x: -x[1])
df_mashupCategory_possibleApiCategoryCount_Table = pd.DataFrame(data=mashupCategory_possibleApiCategoryCount_Table,
                                                                columns=['mashup类别', '有关的api类别数'])
filename = "mashup类别与有关的api类别数对照表.xlsx"
if not os.path.exists("secondQuestion_answer_data/" + filename):
    df_mashupCategory_possibleApiCategoryCount_Table.to_excel("secondQuestion_answer_data/" + filename,
                                                              sheet_name=filename,
                                                              index=False)
else:
    print(filename + " 已存在!")

# 制作并写入 mashup 类别与其对应的必用到的 api 类别、类别数对照表
mashupCategory_mustUseApiCategory_Table.sort(key=lambda x: -x[2])
df_mashupCategory_mustUseApiCategory_Table = pd.DataFrame(data=mashupCategory_mustUseApiCategory_Table,
                                                          columns=['mashup类别', '必使用的api类别',
                                                                   '必使用的api类别数'])
filename = "mashup类别与必使用的api类别及类别数对照表.xlsx"
if not os.path.exists("secondQuestion_answer_data/" + filename):
    df_mashupCategory_mustUseApiCategory_Table.to_excel("secondQuestion_answer_data/" + filename, sheet_name=filename,
                                                        index=False)
else:
    print(filename + " 已存在!")

# 制作并写入 mashup 类别与其使用的 api 类别次数降序排列以及它们的使用次数对照表
df_mashupCategory_mostUsedApiCategory_Table = pd.DataFrame(data=mashupCategory_mostUsedApiCategory_Table,
                                                           columns=['mashup类别',
                                                                    'api类别按使用次数降序,冒号后为使用次数'])
filename = "mashup类别与api类别使用次数降序对照表.xlsx"
if not os.path.exists("secondQuestion_answer_data/" + filename):
    df_mashupCategory_mostUsedApiCategory_Table.to_excel("secondQuestion_answer_data/" + filename, sheet_name=filename,
                                                         index=False)
else:
    print(filename + " 已存在!")
# -------------------------------------------------------------------

# 第三题
# api 按类别兼容性字典, 即 dt_apiCategory_compatibility[api1][api2] 为 api1 和 api2 在所有 mashup 中共同被调用的次数, 若为 0 则说明不兼容
# 相当于是字典嵌套字典
dt_apiCategory_compatibility = {}
api_category_list = list(api_category_set)
for api1 in api_category_list:
    dt_apiCategory_compatibility[api1] = {}
    for api2 in api_category_list:
        dt_apiCategory_compatibility[api1][api2] = 0

for mashupCategory in dt_mashupCategory_allMashup_apiCategory.keys():
    for apiCategorySet in dt_mashupCategory_allMashup_apiCategory[mashupCategory].values():
        if len(apiCategorySet) <= 1:
            continue
        for api1 in apiCategorySet:
            for api2 in apiCategorySet:
                if api1 == api2:
                    continue
                dt_apiCategory_compatibility[api1][api2] += 1

# api 按类别兼容性对照表
apiCategory_Compatibility_Table = [[dt_apiCategory_compatibility[api1][api2] for api1 in api_category_list] for api2 in
                                   api_category_list]
# print(apiCategory_Compatibility_Table)
# 制作并写入 api 按类别兼容性对照表
df_apiCategory_Compatibility_Table = pd.DataFrame(data=apiCategory_Compatibility_Table, index=api_category_list,
                                                  columns=api_category_list)
# 去掉 api 按类别全部不兼容的行和列, 即删除和为 0 的行和列
series = df_apiCategory_Compatibility_Table.sum()
for apiCategory in series.index:
    if series[apiCategory] == 0:
        df_apiCategory_Compatibility_Table.drop([apiCategory], axis=1, inplace=True)
        df_apiCategory_Compatibility_Table.drop([apiCategory], axis=0, inplace=True)
# 把不兼容的 api 兼容性设置为 None, 这样做比起为 0 值更方便观察
df_apiCategory_Compatibility_Table.replace(0, "None", inplace=True)
filename = "api按类别兼容性对照表.xlsx"
if not os.path.exists("thirdQuestion_answer_data/" + filename):
    df_apiCategory_Compatibility_Table.to_excel("thirdQuestion_answer_data/" + filename, sheet_name=filename)
else:
    print(filename + " 已存在!")

# api 按 url 兼容性字典, 即 dt_apiURL_compatibility[api1][api2] 为 api1 和 api2 在所有 mashup 中共同被调用的次数, 若为 0 则说明不兼容
# 相当于是字典嵌套字典
dt_apiURL_compatibility = {}
api_url_list = set()  # 最终会把它转成 list

# 通过 api_url 查找调用过它的全部 mashup 的字典
dt_apiURL_mashup = {}

for i in range(len(df_mashup_invoking_APIs.index)):
    series = df_mashup_invoking_APIs.iloc[i]
    mashup_name, api_url = series['source'], series['target']
    api_url_list.add(api_url)
    # 写入 dt_apiURL_mashup 字典
    if api_url not in dt_apiURL_mashup.keys():
        dt_apiURL_mashup[api_url] = {mashup_name}
    else:
        dt_apiURL_mashup[api_url].add(mashup_name)
api_url_list = list(api_url_list)

for api1 in api_url_list:
    dt_apiURL_compatibility[api1] = {}
    for api2 in api_url_list:
        dt_apiURL_compatibility[api1][api2] = 0
        if api1 != api2:
            dt_apiURL_compatibility[api1][api2] = len(dt_apiURL_mashup[api1] & dt_apiURL_mashup[api2])

# api 按 url 兼容性对照表
apiURL_Compatibility_Table = [[dt_apiURL_compatibility[api1][api2] for api1 in api_url_list] for api2 in
                              api_url_list]
# print(apiURL_Compatibility_Table)
# 制作并写入 api 按 url 兼容性对照表
df_apiURL_Compatibility_Table = pd.DataFrame(data=apiURL_Compatibility_Table, index=api_url_list,
                                             columns=api_url_list)
# 去掉 api 按 url 全部不兼容的行和列, 即删除和为 0 的行和列
series = df_apiURL_Compatibility_Table.sum()
for api_url in series.index:
    if series[api_url] == 0:
        df_apiURL_Compatibility_Table.drop([api_url], axis=1, inplace=True)
        df_apiURL_Compatibility_Table.drop([api_url], axis=0, inplace=True)

# 把不兼容的 api 兼容性设置为 None, 这样做比起为 0 值更方便观察
df_apiURL_Compatibility_Table.replace(0, "None", inplace=True)
filename = "api按url兼容性对照表.xlsx"
if not os.path.exists("thirdQuestion_answer_data/" + filename):
    df_apiURL_Compatibility_Table.to_excel("thirdQuestion_answer_data/" + filename, sheet_name=filename)
else:
    print(filename + " 已存在!")

# 通过 api_url 去查其被使用次数的字典
dt_apiURL_numberOfUse = {}

# 通过 api 类别去查属于此类别的 api_url 的字典
dt_apiCategory_apiURL = {}  # 字典嵌套集合
for i in range(len(df_API.index)):
    series = df_API.iloc[i]
    api_url, category = series['url'], series['c']
    # 由于这是对 df_API 的遍历, 它的 api_url 最全, 故此时也应该对 dt_apiURL_numberOfUse 初始化
    dt_apiURL_numberOfUse[api_url] = 0
    if category not in dt_apiCategory_apiURL.keys():
        dt_apiCategory_apiURL[category] = {api_url}
    else:
        dt_apiCategory_apiURL[category].add(api_url)

for i in range(len(df_mashup_invoking_APIs.index)):
    series = df_mashup_invoking_APIs.iloc[i]
    api_url = series['target']
    dt_apiURL_numberOfUse[api_url] = dt_apiURL_numberOfUse.get(api_url, 0) + 1

# 相同类别的 api 对应 api_url 使用情况表
apiCategory_apiURL_numberOfUse_Table = []
for apiCategory in dt_apiCategory_apiURL.keys():
    # 单纯同一类别下的 api_url 使用情况表, 用作暂存以方便按使用次数排序
    apiCategory_apiURL_numberOfUse_temp = []
    for api_url in dt_apiCategory_apiURL[apiCategory]:
        if dt_apiURL_numberOfUse[api_url] == 0:  # 过滤 api_url 未被使用时对应的数据
            continue
        apiCategory_apiURL_numberOfUse_temp.append([api_url, apiCategory, dt_apiURL_numberOfUse[api_url]])
    apiCategory_apiURL_numberOfUse_temp.sort(key=lambda x: -x[2])
    apiCategory_apiURL_numberOfUse_Table.extend(apiCategory_apiURL_numberOfUse_temp)

# 制作并写入相同类别的 api 对应 api_url 使用情况表
df_apiCategory_apiURL_numberOfUse_Table = pd.DataFrame(data=apiCategory_apiURL_numberOfUse_Table,
                                                       columns=['api(api_url)', 'api所属类别', 'api被使用次数'])
filename = "同一类别对应api使用情况表.xlsx"
if not os.path.exists("thirdQuestion_answer_data/" + filename):
    df_apiCategory_apiURL_numberOfUse_Table.to_excel("thirdQuestion_answer_data/" + filename, sheet_name=filename,
                                                     index=False)
else:
    print(filename + " 已存在!")
